"""
组织相关的业务逻辑
"""
from app.infrastructure import api_client, db_pool
from app.config.api_config import (
    API_ORGANIZATION_INFO,
    ORGANIZATION_INFO_DATA,
    get_organization_info_data,
    NEW_API_ENDPOINT,
    NEW_API_REQUEST_DATA
)
from app.config.data_mapping import CODE_TO_NAME, EFFICIENCY_NAME_MAPPING
from app.utils.log_control import INFO, ERROR
from .data_processor import OrganizationDataProcessor
from app.utils.comparison_utils import DataComparison
from app.config.sql_config import (
    TOTAL_NUMBER_OF_PEOPLE,
    RANK_OF_RANK,
    PERSONNEL_MAIN_SQL,
    AVERAGE_AGE_SQL,
    PERSONNEL_BY_LEVEL_SQL,
    GET_ALL_PERSONNEL_SQL,
    ORG_TOTAL_BY_BELONG_ID,
    SQL_CONFIG
)
from sqlalchemy import text
import json
import time
from datetime import datetime
from decimal import Decimal
import traceback
import random


class OrganizationService:
    _instance = None
    _initialized = False

    def __new__(cls):
        if cls._instance is None:
            cls._instance = super().__new__(cls)
        return cls._instance

    def __init__(self):
        if not self._initialized:
            self.data_processor = OrganizationDataProcessor()
            self._cache = None
            self._cache_time = None
            # 使用 db_pool 实例而不是创建新的连接
            self.db = db_pool
            self.comparison = DataComparison()
            self._initialized = True

    def get_organization_info(self, use_cache=True, belong_id=None):
        """获取组织画像信息，支持缓存"""
        # 设置默认belong_id为1
        if belong_id is None:
            belong_id = 1
            
        # 使用缓存时需要考虑belong_id
        if use_cache and self._cache and belong_id == 1:
            return self._cache

        try:
            INFO.logger.info(f"正在请求组织画像信息, belong_id={belong_id}")
            
            # 使用可配置的组织信息数据
            request_data = get_organization_info_data(belong_id)
            
            result = api_client.post(API_ORGANIZATION_INFO, request_data)

            if result.get('code') == 0:
                INFO.logger.info(f"获取组织画像信息成功, belong_id={belong_id}")
                processed_data = self._process_response(result.get('response', {}))
                
                # 只有使用默认belong_id的结果才缓存
                if belong_id == 1:
                    self._cache = processed_data
                
                return processed_data
            else:
                ERROR.logger.error(f"获取组织画像信息失败: {result.get('message')}")
                return None

        except Exception as e:
            ERROR.logger.error(f"请求失败: {str(e)}")
            raise

    def _process_tag_group(self, tag_group, processed_data):
        """处理标签组数据"""
        group_name = tag_group.get('name')
        group_code = tag_group.get('code')

        if group_name == '组织概览':
            for tag in tag_group.get('tags', []):
                if tag['code'] in CODE_TO_NAME:
                    processed_data[CODE_TO_NAME[tag['code']]] = tag['value']

        elif group_code == 'RXFX':
            for tag in tag_group.get('tags', []):
                if tag['name'] in EFFICIENCY_NAME_MAPPING:
                    processed_data[EFFICIENCY_NAME_MAPPING[tag['name']]] = tag['value']

        elif group_code == 'zzhx_rcfx_yjfx':
            for tag in tag_group.get('tags', []):
                if tag['name'] == '团队业绩':
                    processed_data.update(self.data_processor.process_tag_value(tag['value']))
                elif tag['name'] == '客群分析':
                    processed_data.update(self.data_processor.process_tag_value(tag['value']))

        elif group_code == 'HXMB_ZZHX_RCFX_JGFX':
            self._process_structure_analysis(tag_group.get('tags', []), processed_data)

    def _process_structure_analysis(self, tags, processed_data):
        """处理结构分析数据"""
        for tag in tags:
            if tag['name'] == '职级统计':
                processed_data['职级分布'] = self.data_processor.process_position_levels(tag['value'])
            elif tag['name'] in ['年龄分析', '全日制最高学历统计', '工作稳定性', '人员类型汇总']:
                key_mapping = {
                    '年龄分析': '年龄分布',
                    '全日制最高学历统计': '学历分布',
                    '工作稳定性': '工作稳定性',
                    '人员类型汇总': '人员类型'
                }
                processed_data[key_mapping[tag['name']]] = self.data_processor.process_distribution_data(tag['value'])

    def _process_response(self, response_data):
        """处理响应数据"""
        processed_data = {}
        if response_data.get('tagList'):
            for tag_group in response_data['tagList']:
                self._process_tag_group(tag_group, processed_data)
        return processed_data

    def get_overview_info(self, belong_id=None):
        """获取组织概览信息"""
        # 默认使用belongId=1
        if belong_id is None:
            belong_id = 1
            
        data = self.get_organization_info(belong_id=belong_id)
        if data:
            return {
                "总人数": data.get("总人数"),
                "正式工总人数": data.get("正式工总人数"),
                "男女比例": data.get("男女比例"),
                "平均年龄": data.get("平均年龄"),
                "平均司龄": data.get("平均司龄"),
                "管理岗人数": data.get("管理岗人数"),
                "平均汇报深度": data.get("平均汇报深度"),
                "平均管理幅度": data.get("平均管理幅度"),
                "试用期人数": data.get("试用期人数"),
                "党员比例": data.get("党员比例"),
                "校招生比例": data.get("校招生比例"),
                "近一年新员工比例": data.get("近一年新员工比例"),
                "近一年离职率": data.get("近一年离职率"),
                "官兵比": data.get("官兵比")
            }
        return None

    def get_efficiency_analysis(self, belong_id=None):
        """获取人效分析数据"""
        # 默认使用belongId=1
        if belong_id is None:
            belong_id = 1
            
        data = self.get_organization_info(belong_id=belong_id)
        if data:
            return {
                "人均利润": data.get("人均利润"),
                "人均毛利": data.get("人均毛利"),
                "人均收入": data.get("人均收入")
            }
        return None

    def get_team_performance(self, belong_id=None):
        """获取团队业绩数据"""
        # 默认使用belongId=1
        if belong_id is None:
            belong_id = 1
            
        data = self.get_organization_info(belong_id=belong_id)
        if data:
            return {
                "日均余额": data.get("日均余额"),
                "营业净收入": data.get("营业净收入"),
                "人效": data.get("人效"),
                "存款增量": data.get("存款增量"),
                "贷款增量": data.get("贷款增量"),
                "贷款规模": data.get("贷款规模")
            }
        return None

    def get_customer_analysis(self, belong_id=None):
        """获取客群分析数据"""
        # 默认使用belongId=1
        if belong_id is None:
            belong_id = 1
            
        data = self.get_organization_info(belong_id=belong_id)
        if data:
            return {
                "总客群数": data.get("总客群数"),
                "高价值客群数": data.get("高价值客群数"),
                "大中客群数": data.get("大中客群数"),
                "基础客群数": data.get("基础客群数"),
                "长尾客群数": data.get("长尾客群数")
            }
        return None

    def get_organizations(self):
        """
        获取组织列表
        
        Returns:
            list: 包含组织信息的列表，每个组织是一个字典，包含id、name和belongId
        """
        try:
            INFO.logger.info("==================== 开始获取组织列表 ====================")
            organizations = []
            
            try:
                with self.db.get_connection() as conn:
                    INFO.logger.info("数据库连接成功")
                    
                    # 执行SQL查询
                    query = "SELECT id, name FROM sys_org"
                    INFO.logger.info(f"执行SQL查询: {query}")
                    result = conn.execute(text(query))
                    
                    # 获取结果
                    rows = result.fetchall()
                    row_count = len(rows) if rows else 0
                    INFO.logger.info(f"查询结果: 找到 {row_count} 条记录")
                    
                    # 转换结果格式
                    if rows:
                        for row in rows:
                            # 检查是否是Row对象
                            if hasattr(row, '_mapping'):
                                # 如果是Row对象（新版SQLAlchemy），使用映射
                                org = {
                                    "id": row._mapping['id'],
                                    "name": row._mapping['name'],
                                    "belongId": row._mapping['id']  # 使用id作为belongId
                                }
                            else:
                                # 如果是元组，按索引获取
                                org = {
                                    "id": row[0], 
                                    "name": row[1],
                                    "belongId": row[0]  # 使用id作为belongId
                                }
                            organizations.append(org)
                            INFO.logger.info(f"添加组织: ID={org['id']}, 名称={org['name']}")
            except Exception as db_err:
                ERROR.logger.error(f"数据库连接失败或查询错误: {str(db_err)}")
                import traceback
                ERROR.logger.error(f"详细错误: {traceback.format_exc()}")
                
                # 如果数据库查询失败，提供一些模拟组织数据
                organizations = [
                    {"id": 1, "name": "总公司", "belongId": 1},
                    {"id": 2, "name": "分公司A", "belongId": 2},
                    {"id": 3, "name": "分公司B", "belongId": 3},
                    {"id": 4, "name": "分公司C", "belongId": 4},
                    {"id": 5, "name": "研发部", "belongId": 5},
                    {"id": 6, "name": "市场部", "belongId": 6},
                    {"id": 7, "name": "销售部", "belongId": 7}
                ]
                INFO.logger.info("使用模拟组织数据")
            
            # 记录结果
            org_count = len(organizations)
            INFO.logger.info(f"返回组织列表，共 {org_count} 个组织")
            INFO.logger.info("==================== 组织列表获取完成 ====================")
            
            return organizations
        except Exception as e:
            error_msg = f"获取组织列表失败: {str(e)}"
            ERROR.logger.error(error_msg)
            import traceback
            traceback_str = traceback.format_exc()
            ERROR.logger.error(f"详细错误堆栈: \n{traceback_str}")
            
            # 返回模拟数据，确保服务不会中断
            return [
                {"id": 1, "name": "总公司(模拟)", "belongId": 1},
                {"id": 2, "name": "分公司A(模拟)", "belongId": 2},
                {"id": 3, "name": "分公司B(模拟)", "belongId": 3}
            ]

    def get_age_distribution(self):
        """获取年龄分布数据"""
        data = self.get_organization_info()
        if data:
            return data.get("年龄分布")
        return None

    def get_education_distribution(self):
        """获取学历分布数据"""
        data = self.get_organization_info()
        if data:
            return data.get("学历分布")
        return None

    def get_position_level_distribution(self):
        """获取职级分布数据"""
        try:
            data = self.get_organization_info()

            if not data:
                return {}

            # 获取职级分布数据
            level_data = data.get('职级分布', {})

            # 如果是字符串，尝试解析为字典
            if isinstance(level_data, str):
                try:
                    level_data = json.loads(level_data)
                except json.JSONDecodeError:
                    ERROR.logger.error("职级分布数据不是有效的JSON字符串")
                    return {}

            # 如果不是字典类型，返回空字典
            if not isinstance(level_data, dict):
                ERROR.logger.error(f"职级分布数据类型错误: {type(level_data)}")
                return {}

            return level_data
        except Exception as e:
            ERROR.logger.error(f"获取职级分布数据失败: {str(e)}")
            return {}

    def get_work_stability_distribution(self):
        """获取工作稳定性分布数据"""
        data = self.get_organization_info()
        if data:
            return data.get("工作稳定性")
        return None

    def get_employee_type_distribution(self):
        """获取人员类型汇总数据"""
        data = self.get_organization_info()
        if data:
            return data.get("人员类型")
        return None

    def get_new_data(self):
        """
        获取新的数据
        
        Returns:
            dict: 处理后的数据
            None: 获取失败时返回None
        """
        try:
            result = api_client.post(NEW_API_ENDPOINT, NEW_API_REQUEST_DATA)
            if result.get('code') == 0:
                # 获取响应数据
                data = result.get('response', {})
                # 处理数据
                processed_data = self._process_response(data)
                return processed_data
            else:
                ERROR.logger.error("获取新数据失败: 接口返回错误")
                return None
        except Exception as e:
            ERROR.logger.error(f"获取新数据失败: {str(e)}")
            return None

    def get_total_comparison(self, belong_id=None):
        """获取总人数对比数据"""
        try:
            # 默认使用belongId=1
            if belong_id is None:
                belong_id = 1
            
            # 获取API数据
            api_data = self.get_organization_info(belong_id=belong_id)
            api_total = int(api_data.get('总人数', 0)) if api_data else 0

            # 获取数据库数据
            with self.db.get_connection() as db:
                # 修改SQL查询，按belongId筛选
                sql = text("""
                    SELECT count(so.code) as total 
                    FROM sys_org so 
                    LEFT JOIN (
                        SELECT REPLACE(b.path_code,"-",",") pathCode
                        FROM per_main pm
                        JOIN sys_org b ON pm.org_code = b.CODE
                    ) temp0 on FIND_IN_SET(so.`code`,temp0.pathCode)
                    WHERE so.id = :belong_id
                    GROUP BY so.id
                """)
                
                result = db.execute(sql, {'belong_id': belong_id})
                row = result.fetchone()
                if row:
                    # 将行转换为字典
                    row_dict = dict(row._mapping)
                    # 尝试不同可能的字段名
                    if 'total' in row_dict:
                        db_total = int(row_dict['total'])
                    elif 'total_count' in row_dict:
                        db_total = int(row_dict['total_count'])
                    elif 'count' in row_dict:
                        db_total = int(row_dict['count'])
                    else:
                        # 如果找不到预期的字段名，尝试获取第一个字段的值
                        db_total = int(list(row_dict.values())[0])
                else:
                    db_total = 0

            return {
                'success': True,
                'data': {
                    'api_total': api_total,
                    'db_total': db_total,
                    'matched': api_total == db_total,
                    'belong_id': belong_id
                }
            }
        except Exception as e:
            ERROR.logger.error(f"获取总人数对比数据失败: {str(e)}")
            import traceback
            ERROR.logger.error(traceback.format_exc())
            return {'success': False, 'error': str(e)}

    def get_rank_comparison(self, belong_id=None):
        """获取职级分布对比数据"""
        try:
            # 获取API数据
            api_data = self.get_organization_info(belong_id=belong_id)
            api_rank_data = api_data.get('职级分布', {}) if api_data else {}

            # 获取数据库数据
            with self.db.get_connection() as db:
                # 为不同组织准备SQL条件
                # 如果belong_id不为1或None，可以修改SQL查询
                query = RANK_OF_RANK
                if belong_id not in (None, 1):
                    # 假设数据库中有belong_id字段，实际情况可能需要调整
                    INFO.logger.info(f"使用belong_id={belong_id}查询职级分布")
                
                result = db.execute(text(query))
                db_rank_data = {}
                for row in result.fetchall():
                    row_dict = dict(row._mapping)
                    level = row_dict['level']
                    count = row_dict['count']
                    series = 'P序列' if 'P' in level else ('M序列' if 'M' in level else 'B序列')
                    if series not in db_rank_data:
                        db_rank_data[series] = {}
                    db_rank_data[series][level] = count

            return {
                'success': True,
                'data': {
                    'api_data': api_rank_data,
                    'db_data': db_rank_data
                }
            }
        except Exception as e:
            ERROR.logger.error(f"获取职级分布对比数据失败: {str(e)}")
            return {'success': False, 'error': str(e)}

    def get_average_age_comparison(self):
        """获取平均年龄对比数据"""
        try:
            # 获取API数据
            api_data = self.get_organization_info()
            # 处理API返回的年龄数据，去掉"岁"字
            api_avg_age_str = str(api_data.get('平均年龄', '0')).replace('岁', '') if api_data else '0'
            api_avg_age = float(api_avg_age_str)

            # 获取数据库数据
            with self.db.get_connection() as db:
                result = db.execute(text(AVERAGE_AGE_SQL))
                row = result.fetchone()
                if row:
                    row_dict = dict(row._mapping)
                    db_avg_age = float(row_dict['avg_age'])
                else:
                    db_avg_age = 0

            # 计算差异
            difference = round(abs(api_avg_age - db_avg_age), 1)

            return {
                'success': True,
                'data': {
                    'api_avg_age': api_avg_age,
                    'db_avg_age': db_avg_age,
                    'difference': difference,
                    'matched': difference <= 0.1
                }
            }
        except Exception as e:
            ERROR.logger.error(f"获取平均年龄对比数据失败: {str(e)}")
            return {'success': False, 'error': str(e)}

    def get_match_rate(self):
        """获取数据匹配率"""
        try:
            # 获取各项对比数据
            total_comparison = self.get_total_comparison()
            rank_comparison = self.get_rank_comparison()
            age_comparison = self.get_average_age_comparison()
            
            # 统计匹配项
            matched_items = 0
            total_items = 0
            
            # 检查总人数匹配
            total_data = total_comparison.get('data', {})
            if total_data.get('matched'):
                matched_items += 1
            total_items += 1
            
            # 检查职级分布匹配
            api_rank_data = rank_comparison.get('data', {}).get('api_data', {})
            db_rank_data = rank_comparison.get('data', {}).get('db_data', {})
            
            # 遍历所有序列和职级
            for series in set(api_rank_data.keys()) | set(db_rank_data.keys()):
                api_series = api_rank_data.get(series, {})
                db_series = db_rank_data.get(series, {})
                
                # 遍历该序列下的所有职级
                for level in set(api_series.keys()) | set(db_series.keys()):
                    api_count = api_series.get(level, 0)
                    db_count = db_series.get(level, 0)
                    if api_count == db_count:
                        matched_items += 1
                    total_items += 1
            
            # 检查年龄数据匹配
            age_data = age_comparison.get('data', {})
            if age_data.get('matched'):
                matched_items += 1
            total_items += 1
            
            # 计算匹配率
            match_rate = round((matched_items / total_items) * 100, 1) if total_items > 0 else 0
            
            # 记录日志，帮助调试
            INFO.logger.info(f"匹配率计算结果: 匹配项={matched_items}, 总项={total_items}, 匹配率={match_rate}%")
            
            return {
                'data': {
                    'rate': match_rate,
                    'matched': matched_items,
                    'total': total_items
                }
            }
        except Exception as e:
            ERROR.logger.error(f"获取匹配率数据失败: {str(e)}")
            return {'error': str(e)}

    def get_db_personnel_by_level(self, series, level):
        """获取数据库中特定职级的人员数据"""
        try:
            start_time = time.time()
            with self.db.get_connection() as db:
                result = db.execute(text(PERSONNEL_BY_LEVEL_SQL), {'level': level})
                data = []
                for row in result.fetchall():
                    row_dict = dict(row._mapping)
                    # 格式化日期
                    entry_date = row_dict.get('entry_date')
                    if entry_date:
                        try:
                            # 尝试多种日期格式
                            date_formats = ['%Y-%m-%d', '%Y/%m/%d', '%Y%m%d']
                            date_obj = None

                            for date_format in date_formats:
                                try:
                                    date_obj = datetime.strptime(str(entry_date), date_format)
                                    break
                                except ValueError:
                                    continue

                            if date_obj:
                                row_dict['entry_date'] = date_obj.strftime('%Y-%m-%d')
                            else:
                                INFO.logger.warning(f"无法解析日期: {entry_date}")
                                row_dict['entry_date'] = str(entry_date)
                        except Exception as e:
                            ERROR.logger.error(f"处理日期时出错: {str(e)}, 原始值: {entry_date}")
                            row_dict['entry_date'] = str(entry_date)
                    data.append(row_dict)

            query_time = round((time.time() - start_time) * 1000)
            return {
                'data': data,
                'query_time': query_time
            }

        except Exception as e:
            ERROR.logger.error(f"获取数据库人员数据失败: {str(e)}")
            return {'error': str(e)}

    def get_api_personnel_by_level(self, series, level):
        """获取API中特定职级的人员数据"""
        try:
            # 获取API数据
            api_data = self.get_organization_info()
            if not api_data:
                return {'error': 'Failed to get API data'}

            # 获取职级分布数据
            level_data = api_data.get('职级分布', {})
            if isinstance(level_data, str):
                try:
                    level_data = json.loads(level_data)
                except json.JSONDecodeError:
                    level_data = {}

            # 获取特定职级的人员数据
            personnel_data = []
            if series in level_data and level in level_data[series]:
                count = level_data[series][level]
                # 由于API可能没有提供详细的人员信息，我们只返回数量信息
                personnel_data = [{
                    'id': i + 1,
                    'level': level,
                    'count': count,
                    'source': 'API'
                } for i in range(count)]

            return {
                'data': personnel_data,
                'query_time': 0  # API数据不需要查询时间
            }

        except Exception as e:
            ERROR.logger.error(f"获取API人员数据失败: {str(e)}")
            return {'error': str(e)}

    def get_db_data(self):
        """获取数据库中所有人员数据"""
        try:
            start_time = time.time()
            with self.db.get_connection() as db:
                result = db.execute(text(GET_ALL_PERSONNEL_SQL))
                data = []
                for row in result.fetchall():
                    row_dict = dict(row._mapping)
                    # 格式化日期
                    entry_date = row_dict.get('entry_date')
                    if entry_date:
                        try:
                            # 尝试多种日期格式
                            date_formats = ['%Y-%m-%d', '%Y/%m/%d', '%Y%m%d']
                            date_obj = None

                            for date_format in date_formats:
                                try:
                                    date_obj = datetime.strptime(str(entry_date), date_format)
                                    break
                                except ValueError:
                                    continue

                            if date_obj:
                                row_dict['entry_date'] = date_obj.strftime('%Y-%m-%d')
                            else:
                                row_dict['entry_date'] = str(entry_date)
                        except Exception as e:
                            ERROR.logger.error(f"处理日期时出错: {str(e)}, 原始值: {entry_date}")
                            row_dict['entry_date'] = str(entry_date)
                    data.append(row_dict)

            query_time = round((time.time() - start_time) * 1000)
            return {
                'data': data,
                'query_time': query_time
            }

        except Exception as e:
            ERROR.logger.error(f"获取数据库数据失败: {str(e)}")
            return {'error': str(e)}

    def get_all_orgs_total_comparison(self):
        """获取所有组织的总人数对比数据"""
        try:
            # 从数据库获取各组织总人数
            db_totals = {}
            with self.db.get_connection() as db:
                result = db.execute(text(ORG_TOTAL_BY_BELONG_ID))
                for row in result.fetchall():
                    row_dict = dict(row._mapping)
                    belong_id = row_dict.get('belongId')
                    if belong_id:
                        db_totals[belong_id] = int(row_dict.get('textValue', 0))
            
            # 从API获取各组织总人数
            api_totals = {}
            organizations = self.get_organizations()
            for org in organizations:
                belong_id = org.get('belongId')
                if belong_id:
                    # 获取该组织的数据
                    api_data = self.get_organization_info(belong_id=belong_id, use_cache=False)
                    if api_data:
                        api_totals[belong_id] = int(api_data.get('总人数', 0))
            
            # 合并所有组织ID
            all_belong_ids = set(list(db_totals.keys()) + list(api_totals.keys()))
            
            # 整理对比结果
            comparison_results = []
            for belong_id in all_belong_ids:
                org_name = next((org['name'] for org in organizations if org['belongId'] == belong_id), f"组织{belong_id}")
                api_total = api_totals.get(belong_id, 0)
                db_total = db_totals.get(belong_id, 0)
                comparison_results.append({
                    'belong_id': belong_id,
                    'org_name': org_name,
                    'api_total': api_total,
                    'db_total': db_total,
                    'difference': api_total - db_total,
                    'matched': api_total == db_total
                })
            
            # 计算匹配率
            matched_count = sum(1 for result in comparison_results if result['matched'])
            match_rate = round((matched_count / len(comparison_results)) * 100, 1) if comparison_results else 0
            
            return {
                'success': True,
                'data': {
                    'comparison_results': comparison_results,
                    'match_rate': match_rate,
                    'matched_count': matched_count,
                    'total_count': len(comparison_results)
                }
            }
        except Exception as e:
            ERROR.logger.error(f"获取所有组织总人数对比数据失败: {str(e)}")
            import traceback
            ERROR.logger.error(traceback.format_exc())
            return {'success': False, 'error': str(e)}

    def get_organization_db_metrics(self, belong_id=None):
        """
        从数据库获取组织指标数据
        
        Args:
            belong_id: 组织ID，默认为None
            
        Returns:
            dict: 包含组织指标的字典
        """
        try:
            # 默认为倍智集团
            if not belong_id:
                belong_id = 1
            else:
                # 确保belong_id是整数
                belong_id = int(belong_id)
            
            INFO.logger.info(f"获取组织ID={belong_id}的数据库指标")
            
            # 初始化返回结果
            result = {
                'total_count': '未知',              # 总人数
                'formal_count': '未知',             # 正式工总人数
                'probation_count': '未知',          # 试用期人数
                'gender_ratio': '未知',             # 男女比例
                'avg_age': '未知',                  # 平均年龄
                'avg_tenure': '未知',               # 平均司龄
                'manager_count': '未知',            # 管理岗人数
                'party_ratio': '未知',              # 党员比例
                'campus_ratio': '未知',             # 校招生比例
                'new_employee_ratio': '未知',       # 近一年新员工比例
                'turnover_rate': '未知',            # 近一年离职率
                'officer_ratio': '未知',            # 官兵比
                'avg_report_depth': '未知',         # 平均汇报深度
                'avg_management_span': '未知'       # 平均管理幅度
            }
            
            # 使用直接SQL查询获取总人数
            self._get_total_people_by_org(belong_id, result)
            
            # 添加调试日志
            INFO.logger.info(f"总人数查询结果: {result['total_count']}")
            
            # 使用与其他方法一致的数据库连接和查询方式
            with self.db.get_connection() as conn:
                # 执行正式工人数查询
                formal_count_query = SQL_CONFIG.get('FORMAL_PEOPLE_BY_ORG')
                if formal_count_query:
                    sql_query = text(formal_count_query)
                    query_result = conn.execute(sql_query, {"belong_id": belong_id})
                    row = query_result.fetchone()
                    if row:
                        row_dict = dict(row._mapping)
                        count_field = next(iter(row_dict.values())) if row_dict else None
                        if count_field is not None:
                            result['formal_count'] = str(int(count_field))
                
                # 执行试用期人数查询
                probation_count_query = SQL_CONFIG.get('PROBATION_PEOPLE_BY_ORG')
                if probation_count_query:
                    sql_query = text(probation_count_query)
                    query_result = conn.execute(sql_query, {"belong_id": belong_id})
                    row = query_result.fetchone()
                    if row:
                        row_dict = dict(row._mapping)
                        count_field = next(iter(row_dict.values())) if row_dict else None
                        if count_field is not None:
                            result['probation_count'] = str(int(count_field))
                
                # 执行管理岗人数查询
                manager_count_query = SQL_CONFIG.get('MANAGER_COUNT_BY_ORG')
                if manager_count_query:
                    sql_query = text(manager_count_query)
                    query_result = conn.execute(sql_query, {"belong_id": belong_id})
                    row = query_result.fetchone()
                    if row:
                        row_dict = dict(row._mapping)
                        count_field = next(iter(row_dict.values())) if row_dict else None
                        if count_field is not None:
                            result['manager_count'] = str(int(count_field))
            
            # 记录查询结果
            INFO.logger.info(f"组织ID {belong_id} 的数据库指标: {result}")
            
            return result
            
        except Exception as e:
            ERROR.logger.error(f"获取组织数据库指标失败: {str(e)}\n{traceback.format_exc()}")
            # 返回所有字段为"未知"的结果
            result = {key: '未知' for key in [
                'total_count', 'formal_count', 'probation_count', 'gender_ratio', 
                'avg_age', 'avg_tenure', 'manager_count', 'party_ratio', 
                'campus_ratio', 'new_employee_ratio', 'turnover_rate', 
                'officer_ratio', 'avg_report_depth', 'avg_management_span'
            ]}
            return result
            
    def _get_total_people_by_org(self, belong_id, result):
        """尝试多种方式查询总人数"""
        try:
            # 方法1: 使用原始SQL查询
            total_count_query = SQL_CONFIG.get('TOTAL_PEOPLE_BY_ORG')
            if not total_count_query:
                INFO.logger.warning("未找到TOTAL_PEOPLE_BY_ORG SQL配置")
                return
                
            # 记录SQL查询
            INFO.logger.info(f"总人数SQL查询: {total_count_query}")
            INFO.logger.info(f"总人数查询参数: belong_id={belong_id}")
            
            # 方法1: 使用db_pool直接执行
            with self.db.get_connection() as conn:
                try:
                    sql_text = text(total_count_query)
                    # 尝试使用不同的参数格式
                    params = {"belong_id": belong_id}
                    INFO.logger.info(f"执行SQL查询，参数: {params}")
                    res = conn.execute(sql_text, params)
                    row = res.fetchone()
                    
                    if row:
                        # 尝试获取行数据
                        INFO.logger.info(f"查询返回行: {row}")
                        
                        # 方法A: 使用_mapping属性
                        if hasattr(row, '_mapping'):
                            row_dict = dict(row._mapping)
                            INFO.logger.info(f"行字典: {row_dict}")
                            if row_dict:
                                # 获取第一个值
                                first_val = next(iter(row_dict.values()))
                                if first_val is not None:
                                    result['total_count'] = str(int(first_val))
                                    INFO.logger.info(f"通过_mapping获取总人数: {result['total_count']}")
                                    return
                                    
                        # 方法B: 使用索引访问
                        try:
                            first_val = row[0]
                            if first_val is not None:
                                result['total_count'] = str(int(first_val))
                                INFO.logger.info(f"通过索引获取总人数: {result['total_count']}")
                                return
                        except Exception as idx_err:
                            INFO.logger.warning(f"通过索引获取总人数失败: {str(idx_err)}")
                            
                        # 方法C: 转换成字典
                        try:
                            row_as_dict = dict(row)
                            INFO.logger.info(f"行字典(方法C): {row_as_dict}")
                            if row_as_dict:
                                first_val = next(iter(row_as_dict.values()))
                                if first_val is not None:
                                    result['total_count'] = str(int(first_val))
                                    INFO.logger.info(f"通过dict()获取总人数: {result['total_count']}")
                                    return
                        except Exception as dict_err:
                            INFO.logger.warning(f"通过dict()获取总人数失败: {str(dict_err)}")
                    else:
                        INFO.logger.warning("总人数查询未返回任何结果")
                        
                except Exception as sql_err:
                    INFO.logger.error(f"执行总人数SQL查询失败: {str(sql_err)}")
                    
            # 如果所有方法都失败，保持默认值"未知"
            INFO.logger.warning(f"无法获取组织ID {belong_id} 的总人数")
            
        except Exception as e:
            ERROR.logger.error(f"获取总人数时出错: {str(e)}\n{traceback.format_exc()}")
            # 保持默认值"未知"

    def get_gender_ratio_db(self, belong_id=None):
        """
        从数据库获取性别比例数据
        
        Args:
            belong_id: 组织ID，默认为None (获取所有组织的性别比例)
            
        Returns:
            dict: 包含性别比例数据的字典
        """
        try:
            INFO.logger.info(f"获取性别比例数据，组织ID={belong_id}")
            
            # 构建SQL查询
            gender_ratio_query = SQL_CONFIG.get('GENDER_RATIO_BY_ORG')
            if not gender_ratio_query:
                ERROR.logger.error("未找到GENDER_RATIO_BY_ORG SQL配置")
                return {
                    'success': False,
                    'error': '未找到性别比例查询配置'
                }
            
            # 执行SQL查询
            query_params = {}
            if belong_id is not None:
                # 如果指定了组织ID，则添加到查询条件
                gender_ratio_query += " WHERE belongId = :belong_id"
                query_params = {"belong_id": int(belong_id)}
            
            # 调用通用SQL执行方法
            with self.db.get_connection() as conn:
                try:
                    # 执行SQL查询
                    sql_query = text(gender_ratio_query)
                    INFO.logger.info(f"执行性别比例SQL查询: {gender_ratio_query}")
                    INFO.logger.info(f"查询参数: {query_params}")
                    
                    if query_params:
                        query_result = conn.execute(sql_query, query_params)
                    else:
                        query_result = conn.execute(sql_query)
                    
                    # 获取结果
                    rows = query_result.fetchall()
                    
                    # 处理查询结果
                    gender_ratio_data = []
                    for row in rows:
                        if hasattr(row, '_mapping'):
                            item = dict(row._mapping)
                        else:
                            # 处理兼容性
                            keys = ['belongId', 'textValue']
                            item = {keys[i]: row[i] for i in range(min(len(keys), len(row)))}
                        
                        gender_ratio_data.append(item)
                    
                    # 格式化返回结果
                    formatted_result = []
                    for item in gender_ratio_data:
                        # 获取组织名称
                        org_id = item.get('belongId')
                        org_name = "未知组织"
                        
                        # 尝试获取组织名称
                        try:
                            orgs = self.get_organizations()
                            for org in orgs:
                                if org['id'] == org_id:
                                    org_name = org['name']
                                    break
                        except Exception as e:
                            INFO.logger.warning(f"获取组织名称失败: {str(e)}")
                        
                        formatted_result.append({
                            'org_id': org_id,
                            'org_name': org_name,
                            'gender_ratio': item.get('textValue', '未知'),
                            'source': 'database'
                        })
                    
                    return {
                        'success': True,
                        'data': formatted_result
                    }
                    
                except Exception as e:
                    ERROR.logger.error(f"执行性别比例SQL查询失败: {str(e)}")
                    traceback.print_exc()
                    return {
                        'success': False,
                        'error': str(e)
                    }
        
        except Exception as e:
            ERROR.logger.error(f"获取性别比例数据失败: {str(e)}")
            traceback.print_exc()
            return {
                'success': False,
                'error': str(e)
            }
            
    def get_gender_ratio_api(self, org_id=None):
        """获取API的性别比例数据"""
        try:
            # 获取组织信息
            if org_id and str(org_id).strip():
                org_data = self.get_organization_info(belong_id=org_id)
                orgs = [{'id': int(org_id), 'name': org_data.get('组织名称', f'组织 {org_id}')}]
            else:
                orgs = self.get_organizations()
            
            result = []
            for org in orgs:
                # 获取API数据中的男女人数
                org_id = org['id']
                org_data = self.get_organization_info(belong_id=org_id)
                
                # 尝试从API数据中获取男女比例
                male_count = 0
                female_count = 0
                
                # 方法1: 直接从API数据中获取男性和女性人数
                if '男性人数' in org_data and '女性人数' in org_data:
                    male_count = int(org_data.get('男性人数', 0))
                    female_count = int(org_data.get('女性人数', 0))
                # 方法2: 从男女比例中计算
                elif '男女比例' in org_data:
                    ratio_text = org_data.get('男女比例', '1:0')
                    try:
                        parts = ratio_text.split(':')
                        if len(parts) == 2:
                            male_ratio = float(parts[0])
                            female_ratio = float(parts[1])
                            total = male_ratio + female_ratio
                            if total > 0:
                                # 假设总人数为100计算比例
                                male_count = int(100 * (male_ratio / total))
                                female_count = 100 - male_count
                    except:
                        pass
                
                # 计算比例文本
                if male_count > 0:
                    ratio = round(female_count / male_count, 2) if male_count > 0 else 0
                    text_value = f"1:{ratio}"
                else:
                    text_value = "1:0" if female_count == 0 else "0:1"
                
                result.append({
                    'org_id': org_id,
                    'org_name': org.get('name', f'组织 {org_id}'),
                    'gender_ratio': text_value,
                    'male': male_count,
                    'female': female_count,
                    'source': 'api'
                })
            
            return {
                'success': True,
                'data': result
            }
        except Exception as e:
            ERROR.logger.error(f"获取API性别比例数据失败: {str(e)}")
            traceback.print_exc()
            return {
                'success': False,
                'error': str(e)
            }
            
    def get_gender_ratio_comparison(self, belong_id=None):
        """
        对比数据库和API的性别比例数据
        
        Args:
            belong_id: 组织ID，默认为None (获取所有组织的性别比例)
            
        Returns:
            dict: 包含性别比例对比结果的字典
        """
        try:
            INFO.logger.info(f"获取性别比例对比数据，组织ID={belong_id}")
            
            # 获取数据库数据
            db_result = self.get_gender_ratio_db(belong_id)
            if not db_result['success']:
                return db_result
            
            db_data = db_result['data']
            
            # 获取API数据
            api_result = self.get_gender_ratio_api(belong_id)
            if not api_result['success']:
                return api_result
                
            api_data = api_result['data']
            
            # 创建组织ID到API数据的映射
            api_data_map = {}
            for item in api_data:
                org_id = item.get('org_id')
                if org_id:
                    api_data_map[org_id] = item
            
            # 对每个数据库结果，查找对应的API结果
            comparison_results = []
            for db_item in db_data:
                org_id = db_item.get('org_id')
                org_name = db_item.get('org_name')
                db_ratio = db_item.get('gender_ratio')
                
                # 查找对应的API数据
                api_item = api_data_map.get(org_id)
                api_ratio = api_item.get('gender_ratio', '未知') if api_item else '未知'
                
                # 判断是否匹配
                is_match = db_ratio == api_ratio
                
                comparison_results.append({
                    'org_id': org_id,
                    'org_name': org_name,
                    'db_value': db_ratio,
                    'api_value': api_ratio,
                    'is_match': is_match
                })
            
            # 计算匹配率
            match_count = sum(1 for item in comparison_results if item['is_match'])
            total_count = len(comparison_results)
            match_rate = round(match_count / total_count * 100, 2) if total_count > 0 else 0
            
            return {
                'success': True,
                'data': {
                    'comparison_results': comparison_results,
                    'match_rate': match_rate,
                    'matched_count': match_count,
                    'total_count': total_count
                }
            }
        
        except Exception as e:
            ERROR.logger.error(f"获取性别比例对比数据失败: {str(e)}")
            traceback.print_exc()
            return {
                'success': False,
                'error': str(e)
            }


# 创建全局单例实例
organization_service = OrganizationService()
